package com.yayan.xh.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;

import com.yayan.xh.bean.EnterBean;

public class EnterDao extends ConnectionBase implements IEnterDao {

	private Connection conn = null;
	
	private QueryRunner qRunner = null;
	
	private PreparedStatement ps = null;
	
	private ResultSet rsKey = null;
	
	@Override
	public long saveEnter(Map<String, String> map) throws Exception {
		long r = 0;
		try {
			String sql = "insert into tb_enter(shop_id, m_id, u_id, u_nickname, e_code, e_sginstatus, "
					+ "e_flagstatus, e_status, e_createdatetime) values(?,?,?,?,?,0,0,?,now())";
			conn = getConn();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, map.get("shopId"));
			ps.setObject(2, map.get("matchId"));
			ps.setObject(3, map.get("userId"));
			ps.setObject(4, map.get("nickName"));
			ps.setObject(5, map.get("code"));
			ps.setObject(6, map.get("type"));
			int re = ps.executeUpdate();
			ResultSet rsKey = ps.getGeneratedKeys();
			while(re!=0&&rsKey.next())
			{
				r = rsKey.getLong(1);
			}
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return r;
	}

	@Override
	public List<Map<String, Object>> getEnter(Map<String, String> map) throws Exception {
		List<Map<String, Object>> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select a.m_id as matchId, b.m_name as matchName, a.e_code as code, a.e_sginstatus as sginStatus,"
					+ " a.e_flagstatus as flagStatus, a.e_status as status,  b.m_stardatetime as datetime, b.m_address as address "
					+ "from tb_enter a left join tb_match b on a.m_id=b.m_id where a.shop_id=? and u_id=? and a.e_sginstatus=0 and b.m_status<>2;";
			conn = getConn();
			result =  qRunner.query(conn, sql, new MapListHandler(), map.get("shopId"), map.get("userId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}
	
	@Override
	public List<Map<String, Object>> getJoin(Map<String, String> map) throws Exception {
		List<Map<String, Object>> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select a.m_id as matchId, b.m_name as matchName, a.e_code as code, a.e_sginstatus as sginStatus,"
					+ " a.e_flagstatus as flagStatus, a.e_status as status,  b.m_stardatetime as datetime, b.m_address as address "
					+ "from tb_enter a left join tb_match b on a.m_id=b.m_id where a.shop_id=? and u_id=? and a.e_sginstatus=1";
			conn = getConn();
			result =  qRunner.query(conn, sql, new MapListHandler(), map.get("shopId"), map.get("userId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public boolean signCode(Map<String, String> map) throws Exception {
		boolean f = false;
		Map<String, Object> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select * from tb_enter where shop_id=? and m_id=? and u_id=?";
			conn = getConn();
			result =  qRunner.query(conn, sql, new MapHandler(), map.get("shopId"), map.get("matchId"), map.get("userId"));
			if(null == result || "1".equals(result.get("e_sginstatus"))) {
				return f;
			}
			String updateSql = "update tb_enter set e_sginstatus=1 where shop_id=? and m_id=? and u_id=?";
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(updateSql);
			ps.setObject(1, map.get("shopId"));
			ps.setObject(2, map.get("matchId"));
			ps.setObject(3, map.get("userId"));
			f = ps.executeUpdate() > 0 ? true : false;
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return f;
	}

	@Override
	public List<EnterBean> getEnterBack(Map<String, String> map) throws Exception {
		List<EnterBean> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select * from tb_enter where shop_id=? and m_id=?";
			conn = getConn();
			result =  qRunner.query(conn, sql, new BeanListHandler<EnterBean>(EnterBean.class), map.get("shopId"), map.get("matchId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public List<EnterBean> getLuckDraw(Map<String, String> map) throws Exception {
		List<EnterBean> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select a.*,b.u_avatarUrl from tb_enter a left join tb_user b on a.u_id=b.u_id where a.e_sginstatus=1 and a.e_flagstatus=0 and a.shop_id=? and a.m_id=?";
			conn = getConn();
			result =  qRunner.query(conn, sql, new BeanListHandler<EnterBean>(EnterBean.class), map.get("shopId"), map.get("matchId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public List<EnterBean> getLuckBoys(Map<String, String> map) throws Exception {
		List<EnterBean> result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select a.*,b.u_avatarUrl from tb_enter a left join tb_user b on a.u_id=b.u_id where a.e_sginstatus=1 and a.e_flagstatus=1 and a.shop_id=? and a.m_id=?";
			conn = getConn();
			result =  qRunner.query(conn, sql, new BeanListHandler<EnterBean>(EnterBean.class), map.get("shopId"), map.get("matchId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

	@Override
	public boolean updateLuckBoy(Map<String, String> map) throws Exception {
		boolean f = false;
		try {
			String sql = "update tb_enter set e_flagstatus=1, e_flagtext=? where shop_id=? and e_id=?";
			conn = getConn();
			conn.setAutoCommit(false);
			ps = conn.prepareStatement(sql);
			ps.setObject(1, map.get("flagtext"));
			ps.setObject(2, map.get("shopId"));
			ps.setObject(3, map.get("eId"));
			f = ps.executeUpdate() > 0 ? true : false;
			conn.commit();
			conn.setAutoCommit(true);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return f;
	}

	@Override
	public EnterBean getEnterById(Map<String, String> map) throws Exception {
		EnterBean result = null;
		try {
			qRunner = new QueryRunner();
			String sql = "select * from tb_enter where shop_id=? and m_id=? and u_id=?";
			conn = getConn();
			result =  qRunner.query(conn, sql, new BeanHandler<EnterBean>(EnterBean.class), map.get("shopId"), map.get("matchId"), map.get("userId"));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(conn, ps, rsKey);
		}
		return result;
	}

}
